<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      python/MySQL入门/13-MySQL多表查询综合练习答案 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>python/MySQL入门/13-MySQL多表查询综合练习答案</h2>



  <p class="post-date">2020-10-22</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h1 id="一、综合练习"><a href="#一、综合练习" class="headerlink" title="一、综合练习"></a>一、综合练习</h1><h2 id="1-1-init-sql文件内容"><a href="#1-1-init-sql文件内容" class="headerlink" title="1.1 init.sql文件内容"></a>1.1 init.sql文件内容</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment"> 数据导入：</span></span><br><span class="line"><span class="comment"> Navicat Premium Data Transfer</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment"> Source Server         : localhost</span></span><br><span class="line"><span class="comment"> Source Server Type    : MySQL</span></span><br><span class="line"><span class="comment"> Source Server Version : 50624</span></span><br><span class="line"><span class="comment"> Source Host           : localhost</span></span><br><span class="line"><span class="comment"> Source Database       : sqlexam</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment"> Target Server Type    : MySQL</span></span><br><span class="line"><span class="comment"> Target Server Version : 50624</span></span><br><span class="line"><span class="comment"> File Encoding         : utf-8</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment"> Date: 10/21/2016 06:46:46 AM</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SET</span> <span class="keyword">NAMES</span> utf8;</span><br><span class="line"><span class="keyword">SET</span> FOREIGN_KEY_CHECKS = <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Table structure for `class`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`class`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`class`</span> (</span><br><span class="line">  <span class="string">`cid`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">  <span class="string">`caption`</span> <span class="built_in">varchar</span>(<span class="number">32</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`cid`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> AUTO_INCREMENT=<span class="number">5</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Records of `class`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">BEGIN</span>;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`class`</span> <span class="keyword">VALUES</span> (<span class="string">'1'</span>, <span class="string">'三年二班'</span>), (<span class="string">'2'</span>, <span class="string">'三年三班'</span>), (<span class="string">'3'</span>, <span class="string">'一年二班'</span>), (<span class="string">'4'</span>, <span class="string">'二年九班'</span>);</span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Table structure for `course`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`course`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`course`</span> (</span><br><span class="line">  <span class="string">`cid`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">  <span class="string">`cname`</span> <span class="built_in">varchar</span>(<span class="number">32</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`teacher_id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`cid`</span>),</span><br><span class="line">  <span class="keyword">KEY</span> <span class="string">`fk_course_teacher`</span> (<span class="string">`teacher_id`</span>),</span><br><span class="line">  <span class="keyword">CONSTRAINT</span> <span class="string">`fk_course_teacher`</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (<span class="string">`teacher_id`</span>) <span class="keyword">REFERENCES</span> <span class="string">`teacher`</span> (<span class="string">`tid`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> AUTO_INCREMENT=<span class="number">5</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Records of `course`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">BEGIN</span>;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`course`</span> <span class="keyword">VALUES</span> (<span class="string">'1'</span>, <span class="string">'生物'</span>, <span class="string">'1'</span>), (<span class="string">'2'</span>, <span class="string">'物理'</span>, <span class="string">'2'</span>), (<span class="string">'3'</span>, <span class="string">'体育'</span>, <span class="string">'3'</span>), (<span class="string">'4'</span>, <span class="string">'美术'</span>, <span class="string">'2'</span>);</span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Table structure for `score`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`score`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`score`</span> (</span><br><span class="line">  <span class="string">`sid`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">  <span class="string">`student_id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`course_id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`num`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`sid`</span>),</span><br><span class="line">  <span class="keyword">KEY</span> <span class="string">`fk_score_student`</span> (<span class="string">`student_id`</span>),</span><br><span class="line">  <span class="keyword">KEY</span> <span class="string">`fk_score_course`</span> (<span class="string">`course_id`</span>),</span><br><span class="line">  <span class="keyword">CONSTRAINT</span> <span class="string">`fk_score_course`</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (<span class="string">`course_id`</span>) <span class="keyword">REFERENCES</span> <span class="string">`course`</span> (<span class="string">`cid`</span>),</span><br><span class="line">  <span class="keyword">CONSTRAINT</span> <span class="string">`fk_score_student`</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (<span class="string">`student_id`</span>) <span class="keyword">REFERENCES</span> <span class="string">`student`</span> (<span class="string">`sid`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> AUTO_INCREMENT=<span class="number">53</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Records of `score`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">BEGIN</span>;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`score`</span> <span class="keyword">VALUES</span> (<span class="string">'1'</span>, <span class="string">'1'</span>, <span class="string">'1'</span>, <span class="string">'10'</span>), (<span class="string">'2'</span>, <span class="string">'1'</span>, <span class="string">'2'</span>, <span class="string">'9'</span>), (<span class="string">'5'</span>, <span class="string">'1'</span>, <span class="string">'4'</span>, <span class="string">'66'</span>), (<span class="string">'6'</span>, <span class="string">'2'</span>, <span class="string">'1'</span>, <span class="string">'8'</span>), (<span class="string">'8'</span>, <span class="string">'2'</span>, <span class="string">'3'</span>, <span class="string">'68'</span>), (<span class="string">'9'</span>, <span class="string">'2'</span>, <span class="string">'4'</span>, <span class="string">'99'</span>), (<span class="string">'10'</span>, <span class="string">'3'</span>, <span class="string">'1'</span>, <span class="string">'77'</span>), (<span class="string">'11'</span>, <span class="string">'3'</span>, <span class="string">'2'</span>, <span class="string">'66'</span>), (<span class="string">'12'</span>, <span class="string">'3'</span>, <span class="string">'3'</span>, <span class="string">'87'</span>), (<span class="string">'13'</span>, <span class="string">'3'</span>, <span class="string">'4'</span>, <span class="string">'99'</span>), (<span class="string">'14'</span>, <span class="string">'4'</span>, <span class="string">'1'</span>, <span class="string">'79'</span>), (<span class="string">'15'</span>, <span class="string">'4'</span>, <span class="string">'2'</span>, <span class="string">'11'</span>), (<span class="string">'16'</span>, <span class="string">'4'</span>, <span class="string">'3'</span>, <span class="string">'67'</span>), (<span class="string">'17'</span>, <span class="string">'4'</span>, <span class="string">'4'</span>, <span class="string">'100'</span>), (<span class="string">'18'</span>, <span class="string">'5'</span>, <span class="string">'1'</span>, <span class="string">'79'</span>), (<span class="string">'19'</span>, <span class="string">'5'</span>, <span class="string">'2'</span>, <span class="string">'11'</span>), (<span class="string">'20'</span>, <span class="string">'5'</span>, <span class="string">'3'</span>, <span class="string">'67'</span>), (<span class="string">'21'</span>, <span class="string">'5'</span>, <span class="string">'4'</span>, <span class="string">'100'</span>), (<span class="string">'22'</span>, <span class="string">'6'</span>, <span class="string">'1'</span>, <span class="string">'9'</span>), (<span class="string">'23'</span>, <span class="string">'6'</span>, <span class="string">'2'</span>, <span class="string">'100'</span>), (<span class="string">'24'</span>, <span class="string">'6'</span>, <span class="string">'3'</span>, <span class="string">'67'</span>), (<span class="string">'25'</span>, <span class="string">'6'</span>, <span class="string">'4'</span>, <span class="string">'100'</span>), (<span class="string">'26'</span>, <span class="string">'7'</span>, <span class="string">'1'</span>, <span class="string">'9'</span>), (<span class="string">'27'</span>, <span class="string">'7'</span>, <span class="string">'2'</span>, <span class="string">'100'</span>), (<span class="string">'28'</span>, <span class="string">'7'</span>, <span class="string">'3'</span>, <span class="string">'67'</span>), (<span class="string">'29'</span>, <span class="string">'7'</span>, <span class="string">'4'</span>, <span class="string">'88'</span>), (<span class="string">'30'</span>, <span class="string">'8'</span>, <span class="string">'1'</span>, <span class="string">'9'</span>), (<span class="string">'31'</span>, <span class="string">'8'</span>, <span class="string">'2'</span>, <span class="string">'100'</span>), (<span class="string">'32'</span>, <span class="string">'8'</span>, <span class="string">'3'</span>, <span class="string">'67'</span>), (<span class="string">'33'</span>, <span class="string">'8'</span>, <span class="string">'4'</span>, <span class="string">'88'</span>), (<span class="string">'34'</span>, <span class="string">'9'</span>, <span class="string">'1'</span>, <span class="string">'91'</span>), (<span class="string">'35'</span>, <span class="string">'9'</span>, <span class="string">'2'</span>, <span class="string">'88'</span>), (<span class="string">'36'</span>, <span class="string">'9'</span>, <span class="string">'3'</span>, <span class="string">'67'</span>), (<span class="string">'37'</span>, <span class="string">'9'</span>, <span class="string">'4'</span>, <span class="string">'22'</span>), (<span class="string">'38'</span>, <span class="string">'10'</span>, <span class="string">'1'</span>, <span class="string">'90'</span>), (<span class="string">'39'</span>, <span class="string">'10'</span>, <span class="string">'2'</span>, <span class="string">'77'</span>), (<span class="string">'40'</span>, <span class="string">'10'</span>, <span class="string">'3'</span>, <span class="string">'43'</span>), (<span class="string">'41'</span>, <span class="string">'10'</span>, <span class="string">'4'</span>, <span class="string">'87'</span>), (<span class="string">'42'</span>, <span class="string">'11'</span>, <span class="string">'1'</span>, <span class="string">'90'</span>), (<span class="string">'43'</span>, <span class="string">'11'</span>, <span class="string">'2'</span>, <span class="string">'77'</span>), (<span class="string">'44'</span>, <span class="string">'11'</span>, <span class="string">'3'</span>, <span class="string">'43'</span>), (<span class="string">'45'</span>, <span class="string">'11'</span>, <span class="string">'4'</span>, <span class="string">'87'</span>), (<span class="string">'46'</span>, <span class="string">'12'</span>, <span class="string">'1'</span>, <span class="string">'90'</span>), (<span class="string">'47'</span>, <span class="string">'12'</span>, <span class="string">'2'</span>, <span class="string">'77'</span>), (<span class="string">'48'</span>, <span class="string">'12'</span>, <span class="string">'3'</span>, <span class="string">'43'</span>), (<span class="string">'49'</span>, <span class="string">'12'</span>, <span class="string">'4'</span>, <span class="string">'87'</span>), (<span class="string">'52'</span>, <span class="string">'13'</span>, <span class="string">'3'</span>, <span class="string">'87'</span>);</span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Table structure for `student`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`student`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`student`</span> (</span><br><span class="line">  <span class="string">`sid`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">  <span class="string">`gender`</span> <span class="built_in">char</span>(<span class="number">1</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`class_id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="string">`sname`</span> <span class="built_in">varchar</span>(<span class="number">32</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`sid`</span>),</span><br><span class="line">  <span class="keyword">KEY</span> <span class="string">`fk_class`</span> (<span class="string">`class_id`</span>),</span><br><span class="line">  <span class="keyword">CONSTRAINT</span> <span class="string">`fk_class`</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (<span class="string">`class_id`</span>) <span class="keyword">REFERENCES</span> <span class="string">`class`</span> (<span class="string">`cid`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> AUTO_INCREMENT=<span class="number">17</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Records of `student`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">BEGIN</span>;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`student`</span> <span class="keyword">VALUES</span> (<span class="string">'1'</span>, <span class="string">'男'</span>, <span class="string">'1'</span>, <span class="string">'理解'</span>), (<span class="string">'2'</span>, <span class="string">'女'</span>, <span class="string">'1'</span>, <span class="string">'钢蛋'</span>), (<span class="string">'3'</span>, <span class="string">'男'</span>, <span class="string">'1'</span>, <span class="string">'张三'</span>), (<span class="string">'4'</span>, <span class="string">'男'</span>, <span class="string">'1'</span>, <span class="string">'张一'</span>), (<span class="string">'5'</span>, <span class="string">'女'</span>, <span class="string">'1'</span>, <span class="string">'张二'</span>), (<span class="string">'6'</span>, <span class="string">'男'</span>, <span class="string">'1'</span>, <span class="string">'张四'</span>), (<span class="string">'7'</span>, <span class="string">'女'</span>, <span class="string">'2'</span>, <span class="string">'铁锤'</span>), (<span class="string">'8'</span>, <span class="string">'男'</span>, <span class="string">'2'</span>, <span class="string">'李三'</span>), (<span class="string">'9'</span>, <span class="string">'男'</span>, <span class="string">'2'</span>, <span class="string">'李一'</span>), (<span class="string">'10'</span>, <span class="string">'女'</span>, <span class="string">'2'</span>, <span class="string">'李二'</span>), (<span class="string">'11'</span>, <span class="string">'男'</span>, <span class="string">'2'</span>, <span class="string">'李四'</span>), (<span class="string">'12'</span>, <span class="string">'女'</span>, <span class="string">'3'</span>, <span class="string">'如花'</span>), (<span class="string">'13'</span>, <span class="string">'男'</span>, <span class="string">'3'</span>, <span class="string">'刘三'</span>), (<span class="string">'14'</span>, <span class="string">'男'</span>, <span class="string">'3'</span>, <span class="string">'刘一'</span>), (<span class="string">'15'</span>, <span class="string">'女'</span>, <span class="string">'3'</span>, <span class="string">'刘二'</span>), (<span class="string">'16'</span>, <span class="string">'男'</span>, <span class="string">'3'</span>, <span class="string">'刘四'</span>);</span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Table structure for `teacher`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`teacher`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`teacher`</span> (</span><br><span class="line">  <span class="string">`tid`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">  <span class="string">`tname`</span> <span class="built_in">varchar</span>(<span class="number">32</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`tid`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> AUTO_INCREMENT=<span class="number">6</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">--  Records of `teacher`</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">BEGIN</span>;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`teacher`</span> <span class="keyword">VALUES</span> (<span class="string">'1'</span>, <span class="string">'张磊老师'</span>), (<span class="string">'2'</span>, <span class="string">'李平老师'</span>), (<span class="string">'3'</span>, <span class="string">'刘海燕老师'</span>), (<span class="string">'4'</span>, <span class="string">'朱云海老师'</span>), (<span class="string">'5'</span>, <span class="string">'李杰老师'</span>);</span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SET</span> FOREIGN_KEY_CHECKS = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h2 id="1-2-从init-sql文件中导入数据"><a href="#1-2-从init-sql文件中导入数据" class="headerlink" title="1.2 从init.sql文件中导入数据"></a>1.2 从init.sql文件中导入数据</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 准备表、记录</span></span><br><span class="line">mysql&gt; create database db1;</span><br><span class="line">mysql&gt; use db1;</span><br><span class="line">mysql&gt; source /root/init.sql</span><br></pre></td></tr></table></figure>

<p>[<img src="https://tva1.sinaimg.cn/large/0081Kckwgy1gjzdssswiqj318m0tetmq.jpg" alt="202-MySQL多表查询-01.png"></p>
<h2 id="1-3-基础练习"><a href="#1-3-基础练习" class="headerlink" title="1.3 基础练习"></a>1.3 基础练习</h2><ol>
<li>查询男生、女生的人数；</li>
<li>查询姓“张”的学生名单；</li>
<li>课程平均分从高到低显示</li>
<li>查询有课程成绩小于60分的同学的学号、姓名；</li>
<li>查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；</li>
<li>查询出只选修了一门课程的全部学生的学号和姓名；</li>
<li>查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；</li>
<li>查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；</li>
<li>查询“生物”课程比“物理”课程成绩高的所有学生的学号；</li>
<li>查询平均成绩大于60分的同学的学号和平均成绩;</li>
<li>查询所有同学的学号、姓名、选课数、总成绩；</li>
<li>查询姓“李”的老师的个数；</li>
<li>查询没学过“张磊老师”课的同学的学号、姓名；</li>
<li>查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；</li>
<li>查询学过“李平老师”所教的所有课的同学的学号、姓名；</li>
</ol>
<h2 id="1-4-进阶练习"><a href="#1-4-进阶练习" class="headerlink" title="1.4 进阶练习"></a>1.4 进阶练习</h2><ol>
<li>查询没有学全所有课的同学的学号、姓名；</li>
<li>查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；</li>
<li>删除学习“叶平”老师课的SC表记录；</li>
<li>向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；</li>
<li>按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；</li>
<li>查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；</li>
<li>按各科平均成绩从低到高和及格率的百分数从高到低顺序；</li>
<li>查询各科成绩前三名的记录:(不考虑成绩并列情况)</li>
<li>查询每门课程被选修的学生数；</li>
<li>查询同名同姓学生名单，并统计同名人数；</li>
<li>查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；</li>
<li>查询平均成绩大于85的所有学生的学号. 姓名和平均成绩；</li>
<li>查询课程名称为“数学”，且分数低于60的学生姓名和分数；</li>
<li>查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；</li>
<li>求选了课程的学生人数</li>
<li>查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；</li>
<li>查询各个课程及相应的选修人数；</li>
<li>查询不同课程但成绩相同的学生的学号、课程号、学生成绩；</li>
<li>查询每门课程成绩最好的前两名；</li>
<li>检索至少选修两门课程的学生学号；</li>
<li>查询全部学生都选修的课程的课程号和课程名；</li>
<li>查询没学过“叶平”老师讲授的任一门课程的学生姓名；</li>
<li>查询两门以上不及格课程的同学的学号及其平均成绩；</li>
<li>检索“004”课程分数小于60，按分数降序排列的同学学号；</li>
<li>删除“002”同学的“001”课程的成绩；</li>
</ol>
<h1 id="二、基础练习答案"><a href="#二、基础练习答案" class="headerlink" title="二、基础练习答案"></a>二、基础练习答案</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br></pre></td><td class="code"><pre><span class="line">1、查询“生物”课程比“物理”课程成绩高的所有学生的学号；</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span>( </span><br><span class="line"></span><br><span class="line"> (<span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> (<span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> cname = <span class="string">'生物'</span>)) t1  </span><br><span class="line"></span><br><span class="line"><span class="keyword">left</span> <span class="keyword">join</span> </span><br><span class="line"></span><br><span class="line"> (<span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> (<span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> cname = <span class="string">'物理'</span>)) t2  </span><br><span class="line"></span><br><span class="line"><span class="keyword">on</span>  t1.student_id = t2.student_id) </span><br><span class="line"></span><br><span class="line"><span class="keyword">where</span> t1.num &gt; t2.num;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">2、查询平均成绩大于60分的同学的学号和平均成绩;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查看每个同学的平均分数</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_id,<span class="keyword">avg</span>(<span class="keyword">num</span>) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 在筛选成绩大于60分的同学的学号和平均成绩；</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># select student_id,avg(num) from score group by student_id having avg(num) &gt; 60;</span></span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">3、查询所有同学的学号、姓名、选课数、总成绩；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查看每个同学的总成绩</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_id,<span class="keyword">sum</span>(<span class="keyword">num</span>) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 学生和课程的关系只有成绩表中存在，因此要获取每个学生选择的课程，需要通过score表</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(<span class="keyword">sid</span>),student_id <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 将上面两步合并</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sum</span>(<span class="keyword">num</span>),<span class="keyword">count</span>(<span class="keyword">sid</span>),student_id <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 将学生的信息和成绩选课情况拼在一起</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname,sum_num ,count_stu </span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> student  </span><br><span class="line"></span><br><span class="line"><span class="keyword">left</span> <span class="keyword">join</span> </span><br><span class="line"></span><br><span class="line"> (<span class="keyword">select</span> <span class="keyword">sum</span>(<span class="keyword">num</span>) sum_num,<span class="keyword">count</span>(<span class="keyword">sid</span>) count_stu,student_id <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id) t2  </span><br><span class="line"></span><br><span class="line"><span class="keyword">on</span>  <span class="keyword">sid</span> = student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 还可以更严谨，那些没有选课的同学选课数和总成绩应该是0</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname,</span><br><span class="line"></span><br><span class="line"> (</span><br><span class="line"></span><br><span class="line">           <span class="keyword">CASE</span></span><br><span class="line"></span><br><span class="line">           <span class="keyword">WHEN</span> sum_num <span class="keyword">is</span>  <span class="literal">null</span> <span class="keyword">THEN</span> <span class="number">0</span>   </span><br><span class="line"></span><br><span class="line">   <span class="keyword">ELSE</span> sum_num</span><br><span class="line"></span><br><span class="line">           <span class="keyword">END</span></span><br><span class="line"></span><br><span class="line">         ) <span class="keyword">as</span> sum_num ,</span><br><span class="line"></span><br><span class="line"> (</span><br><span class="line"></span><br><span class="line">           <span class="keyword">CASE</span></span><br><span class="line"></span><br><span class="line">           <span class="keyword">WHEN</span> count_stu <span class="keyword">is</span>  <span class="literal">null</span> <span class="keyword">THEN</span> <span class="number">0</span>   </span><br><span class="line"></span><br><span class="line">   <span class="keyword">ELSE</span> count_stu</span><br><span class="line"></span><br><span class="line">           <span class="keyword">END</span></span><br><span class="line"></span><br><span class="line">         ) <span class="keyword">as</span> count_stu </span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> student  </span><br><span class="line"></span><br><span class="line"><span class="keyword">left</span> <span class="keyword">join</span> </span><br><span class="line"></span><br><span class="line"> (<span class="keyword">select</span> <span class="keyword">sum</span>(<span class="keyword">num</span>) sum_num,<span class="keyword">count</span>(<span class="keyword">sid</span>) count_stu,student_id <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id) t2  </span><br><span class="line"></span><br><span class="line"><span class="keyword">on</span>  <span class="keyword">sid</span> = student_id;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">4、查询姓“李”的老师的个数；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到所有姓李的</span></span><br><span class="line"></span><br><span class="line"> <span class="comment"># 方法一</span></span><br><span class="line"></span><br><span class="line"> <span class="comment"># select * from teacher where tname like '李%';</span></span><br><span class="line"></span><br><span class="line"> <span class="comment"># 方法二</span></span><br><span class="line"></span><br><span class="line"> <span class="comment"># select * from teacher where tname regexp '^李';</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 统计个数</span></span><br><span class="line"></span><br><span class="line"> <span class="keyword">select</span> <span class="keyword">count</span>(tid) <span class="keyword">from</span> teacher <span class="keyword">where</span> tname regexp <span class="string">'^李'</span>;</span><br><span class="line"></span><br><span class="line"> 或者</span><br><span class="line"></span><br><span class="line"> <span class="keyword">select</span> <span class="keyword">count</span>(<span class="keyword">id</span>) <span class="keyword">from</span> teacher <span class="keyword">where</span> tname <span class="keyword">like</span> <span class="string">'李%'</span>;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">5、查询没学过“张磊老师”课的同学的学号、姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到张磊老师的id </span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname == <span class="string">'张磊老师'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到张磊老师所教课程</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id = (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname = <span class="string">'张磊老师'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到所有学习这门课的学生id</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = (<span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id = (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname = <span class="string">'张磊老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到没有学过这门课的学生对应的学生学号、姓名</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">not</span> <span class="keyword">in</span> </span><br><span class="line"></span><br><span class="line"> (<span class="keyword">select</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = (<span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id = (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname = <span class="string">'张磊老师'</span>))</span><br><span class="line"></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查询学习课程id为1的所有学生</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查询学习课程id为2的所有学生</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">2</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 把这两张表按照学生的id 内连接起来 去掉只学习某一门课程的学生</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> t1.student_id <span class="keyword">from</span></span><br><span class="line"></span><br><span class="line">(<span class="keyword">select</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">1</span>)  t1</span><br><span class="line"></span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line"></span><br><span class="line">(<span class="keyword">select</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">2</span>) t2</span><br><span class="line"></span><br><span class="line"><span class="keyword">on</span> t1.student_id = t2.student_id</span><br><span class="line"></span><br><span class="line"><span class="comment"># 根据学号在学生表中找到对应的姓名</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">in</span> (<span class="keyword">select</span> t1.student_id <span class="keyword">from</span> (<span class="keyword">select</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">1</span>)  t1 <span class="keyword">inner</span> <span class="keyword">join</span> (<span class="keyword">select</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">2</span>) t2 <span class="keyword">on</span> t1.student_id = t2.student_id);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">7、查询学过“李平老师”所教的所有课的同学的学号、姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment">#找到李平老师的tid</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到李平老师教的所有课程cid</span></span><br><span class="line"></span><br><span class="line"> <span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到李平老师教的所有课程数</span></span><br><span class="line"></span><br><span class="line"> <span class="keyword">select</span> <span class="keyword">count</span>(cid) <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到所有学习李平老师课程的学生</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> ( <span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看所有学习李平老师课程的学生选课数</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_id,<span class="keyword">count</span>(course_id) <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> ( <span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>)) <span class="keyword">group</span> <span class="keyword">by</span> student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到所有选择了李平老师所有课程的学生id</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span>  student_id <span class="keyword">from</span> (</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_id,<span class="keyword">count</span>(course_id) course_count <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> ( <span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>)) <span class="keyword">group</span> <span class="keyword">by</span> student_id) t1</span><br><span class="line"></span><br><span class="line"><span class="keyword">where</span> t1.course_count =</span><br><span class="line"></span><br><span class="line">(<span class="keyword">select</span> <span class="keyword">count</span>(cid) <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到学生的其他信息</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">in</span> (</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span>  student_id <span class="keyword">from</span> (</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_id,<span class="keyword">count</span>(course_id) course_count <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> ( <span class="keyword">select</span> cid <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>)) <span class="keyword">group</span> <span class="keyword">by</span> student_id) t1</span><br><span class="line"></span><br><span class="line"><span class="keyword">where</span> t1.course_count =</span><br><span class="line"></span><br><span class="line">(<span class="keyword">select</span> <span class="keyword">count</span>(cid) <span class="keyword">from</span> course <span class="keyword">where</span> teacher_id <span class="keyword">in</span> (<span class="keyword">select</span> tid <span class="keyword">from</span> teacher <span class="keyword">where</span> tname =<span class="string">'李平老师'</span>))</span><br><span class="line"></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先找到每个学生的课程编号“1”的和课程编号“2”的成绩组成一张表</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> t1.student_id <span class="keyword">from</span> (<span class="keyword">select</span> <span class="keyword">num</span> num2,student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">2</span>) t2 <span class="keyword">inner</span> <span class="keyword">join</span> (<span class="keyword">select</span> student_id,<span class="keyword">num</span> num1 <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">1</span>) t1 <span class="keyword">on</span> t1.student_id = t2.student_id</span><br><span class="line"></span><br><span class="line"><span class="comment"># 再找到课程编号“2”的成绩比课程编号“1”课程低的所有学生的学号</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> t1.student_id <span class="keyword">from</span> (<span class="keyword">select</span> <span class="keyword">num</span> num2,student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">2</span>) t2 <span class="keyword">inner</span> <span class="keyword">join</span> (<span class="keyword">select</span> student_id,<span class="keyword">num</span> num1 <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">1</span>) t1 <span class="keyword">on</span> t1.student_id = t2.student_id <span class="keyword">where</span> num2 &lt; num1</span><br><span class="line"></span><br><span class="line"><span class="comment"># 再找到所有学生的学号、姓名</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">in</span>(<span class="keyword">select</span> t1.student_id <span class="keyword">from</span> (<span class="keyword">select</span> <span class="keyword">num</span> num2,student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">2</span>) t2 <span class="keyword">inner</span> <span class="keyword">join</span> (<span class="keyword">select</span> student_id,<span class="keyword">num</span> num1 <span class="keyword">from</span> score <span class="keyword">where</span> course_id = <span class="number">1</span>) t1 <span class="keyword">on</span> t1.student_id = t2.student_id <span class="keyword">where</span> num2 &lt; num1);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">9、查询有课程成绩小于60分的同学的学号、姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查询成绩小于60分的同学的学号</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> <span class="keyword">num</span> &lt; <span class="number">60</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 再查询有课程成绩小于60分的同学的学号、姓名</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">distinct</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> <span class="keyword">num</span> &lt; <span class="number">60</span>);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">10、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先看看学号为1的同学都学了哪些课程</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> course_id <span class="keyword">from</span> score <span class="keyword">where</span> student_id = <span class="number">1</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到学习 学号为1的同学所学课程 的学号</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> (<span class="keyword">select</span> course_id <span class="keyword">from</span> score <span class="keyword">where</span> student_id = <span class="number">1</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">#  找到学习 学号为1的同学所学课程 的学号\姓名</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">distinct</span> student_id <span class="keyword">from</span> score <span class="keyword">where</span> course_id <span class="keyword">in</span> (<span class="keyword">select</span> course_id <span class="keyword">from</span> score <span class="keyword">where</span> student_id = <span class="number">1</span>));</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">11、课程平均分从高到低显示</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> course_id,<span class="keyword">avg</span>(<span class="keyword">num</span>) avg_num <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> course_id <span class="keyword">order</span> <span class="keyword">by</span> avg_num <span class="keyword">desc</span>;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">12、查询出只选修了一门课程的全部学生的学号和姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询出只选修了一门课程的全部学生的学号</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> student_id,<span class="keyword">count</span>(student_id) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id <span class="keyword">having</span> <span class="keyword">count</span>(student_id) =<span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询出只选修了一门课程的全部学生的学号和姓名；</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">in</span> (<span class="keyword">select</span> student_id <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> student_id <span class="keyword">having</span> <span class="keyword">count</span>(student_id) =<span class="number">1</span>);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">13、查询男生、女生的人数；</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> gender,<span class="keyword">count</span>(<span class="keyword">sid</span>) <span class="keyword">from</span> student <span class="keyword">group</span> <span class="keyword">by</span> gender;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">14、查询姓“张”的学生名单；</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> sname <span class="keyword">like</span> <span class="string">'张%'</span>;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">15、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询成绩的最高分</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> course_id c1,<span class="keyword">max</span>(<span class="keyword">num</span>) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> course_id</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询成绩的最低分</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> course_id c1,<span class="keyword">min</span>(<span class="keyword">num</span>) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> course_id</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询成绩的最高分和最低分拼接</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> ( (<span class="keyword">select</span> course_id c1,<span class="keyword">max</span>(<span class="keyword">num</span>) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> course_id) t1 <span class="keyword">inner</span> <span class="keyword">join</span> (<span class="keyword">select</span> course_id c2,<span class="keyword">min</span>(<span class="keyword">num</span>) <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> course_id) t2 <span class="keyword">on</span> t1.c1 = t2.c2 );</span><br><span class="line"></span><br><span class="line"><span class="comment"># 格式整理</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> t1.c1,t1.max_num,t2.min_num <span class="keyword">from</span> ( (<span class="keyword">select</span> course_id c1,<span class="keyword">max</span>(<span class="keyword">num</span>) max_num <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> course_id) t1 <span class="keyword">inner</span> <span class="keyword">join</span> (<span class="keyword">select</span> course_id c2,<span class="keyword">min</span>(<span class="keyword">num</span>) min_num <span class="keyword">from</span> score <span class="keyword">group</span> <span class="keyword">by</span> course_id) t2 <span class="keyword">on</span> t1.c1 = t2.c2 );</span><br></pre></td></tr></table></figure>

<h1 id="三、进阶练习答案"><a href="#三、进阶练习答案" class="headerlink" title="三、进阶练习答案"></a>三、进阶练习答案</h1><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br><span class="line">306</span><br><span class="line">307</span><br><span class="line">308</span><br><span class="line">309</span><br><span class="line">310</span><br><span class="line">311</span><br><span class="line">312</span><br><span class="line">313</span><br><span class="line">314</span><br><span class="line">315</span><br><span class="line">316</span><br><span class="line">317</span><br><span class="line">318</span><br><span class="line">319</span><br><span class="line">320</span><br><span class="line">321</span><br><span class="line">322</span><br><span class="line">323</span><br><span class="line">324</span><br><span class="line">325</span><br><span class="line">326</span><br><span class="line">327</span><br><span class="line">328</span><br><span class="line">329</span><br><span class="line">330</span><br><span class="line">331</span><br><span class="line">332</span><br><span class="line">333</span><br><span class="line">334</span><br><span class="line">335</span><br><span class="line">336</span><br><span class="line">337</span><br><span class="line">338</span><br><span class="line">339</span><br><span class="line">340</span><br><span class="line">341</span><br><span class="line">342</span><br><span class="line">343</span><br><span class="line">344</span><br><span class="line">345</span><br><span class="line">346</span><br><span class="line">347</span><br><span class="line">348</span><br><span class="line">349</span><br><span class="line">350</span><br><span class="line">351</span><br><span class="line">352</span><br><span class="line">353</span><br><span class="line">354</span><br><span class="line">355</span><br><span class="line">356</span><br><span class="line">357</span><br><span class="line">358</span><br><span class="line">359</span><br><span class="line">360</span><br><span class="line">361</span><br><span class="line">362</span><br><span class="line">363</span><br><span class="line">364</span><br><span class="line">365</span><br><span class="line">366</span><br><span class="line">367</span><br><span class="line">368</span><br><span class="line">369</span><br><span class="line">370</span><br><span class="line">371</span><br><span class="line">372</span><br><span class="line">373</span><br><span class="line">374</span><br><span class="line">375</span><br><span class="line">376</span><br><span class="line">377</span><br><span class="line">378</span><br><span class="line">379</span><br><span class="line">380</span><br><span class="line">381</span><br><span class="line">382</span><br><span class="line">383</span><br><span class="line">384</span><br><span class="line">385</span><br><span class="line">386</span><br><span class="line">387</span><br><span class="line">388</span><br><span class="line">389</span><br><span class="line">390</span><br><span class="line">391</span><br><span class="line">392</span><br><span class="line">393</span><br><span class="line">394</span><br><span class="line">395</span><br><span class="line">396</span><br><span class="line">397</span><br><span class="line">398</span><br><span class="line">399</span><br><span class="line">400</span><br><span class="line">401</span><br><span class="line">402</span><br><span class="line">403</span><br><span class="line">404</span><br><span class="line">405</span><br><span class="line">406</span><br><span class="line">407</span><br><span class="line">408</span><br><span class="line">409</span><br><span class="line">410</span><br><span class="line">411</span><br><span class="line">412</span><br><span class="line">413</span><br><span class="line">414</span><br><span class="line">415</span><br><span class="line">416</span><br><span class="line">417</span><br><span class="line">418</span><br><span class="line">419</span><br><span class="line">420</span><br><span class="line">421</span><br><span class="line">422</span><br><span class="line">423</span><br><span class="line">424</span><br><span class="line">425</span><br><span class="line">426</span><br><span class="line">427</span><br><span class="line">428</span><br><span class="line">429</span><br><span class="line">430</span><br><span class="line">431</span><br><span class="line">432</span><br><span class="line">433</span><br><span class="line">434</span><br><span class="line">435</span><br><span class="line">436</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1</span>、查询没有学全所有课的同学的学号、姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先统计一共有多少门课程</span></span><br><span class="line"></span><br><span class="line">select count(cid) <span class="keyword">from</span> course;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看每个学生选择的课程书</span></span><br><span class="line"></span><br><span class="line">select count(course_id) <span class="keyword">from</span> score group by student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询所学课程数小于总课程数的学生学号</span></span><br><span class="line"></span><br><span class="line">select student_id</span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> (select count(course_id) c_course_id,student_id <span class="keyword">from</span> score group by student_id) t1 </span><br><span class="line"></span><br><span class="line">where t1.c_course_id &lt;  (select count(cid) <span class="keyword">from</span> course) ;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查询没有学全所有课的同学的学号、姓名；</span></span><br><span class="line"></span><br><span class="line">select sid,sname <span class="keyword">from</span> student where sid <span class="keyword">in</span> (</span><br><span class="line"></span><br><span class="line"> select student_id <span class="keyword">from</span> (select count(course_id) c_course_id,student_id <span class="keyword">from</span> score group by student_id</span><br><span class="line"></span><br><span class="line"> ) t1 where t1.c_course_id &lt;  (select count(cid) <span class="keyword">from</span> course)</span><br><span class="line"></span><br><span class="line">) ;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">2</span>、查询和“<span class="number">002</span>”号的同学学习的课程完全相同的其他同学学号和姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查询2号同学学了哪些课程</span></span><br><span class="line"></span><br><span class="line">select * <span class="keyword">from</span> score where student_id =<span class="number">2</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到学习了2号同学没学习课程的所有同学（找到所有和2号同学学习的课程不一样的同学）</span></span><br><span class="line"></span><br><span class="line">select student_id <span class="keyword">from</span> score where course_id <span class="keyword">not</span> <span class="keyword">in</span> (select course_id <span class="keyword">from</span> score where student_id=<span class="number">2</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到score表中所有的学生并且把 2号同学 以及（和2号同学学习的课程不一样的同学）排除出去</span></span><br><span class="line"></span><br><span class="line">select student_id <span class="keyword">from</span> score where student_id <span class="keyword">not</span> <span class="keyword">in</span> (select student_id <span class="keyword">from</span> score where course_id <span class="keyword">not</span> <span class="keyword">in</span> (select course_id <span class="keyword">from</span> score where student_id=<span class="number">2</span>)) <span class="keyword">and</span> student_id !=<span class="number">2</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 对剩余的和2号同学所选课程没有不同的同学所选课程数进行统计，如果和2号同学的课程数相同，就是选择了相同的课程</span></span><br><span class="line"></span><br><span class="line">select student_id <span class="keyword">from</span> score where student_id <span class="keyword">not</span> <span class="keyword">in</span> (</span><br><span class="line"></span><br><span class="line"> select student_id <span class="keyword">from</span> score where course_id <span class="keyword">not</span> <span class="keyword">in</span> (select course_id <span class="keyword">from</span> score where student_id=<span class="number">2</span>)</span><br><span class="line"></span><br><span class="line"> ) <span class="keyword">and</span> student_id !=<span class="number">2</span></span><br><span class="line"></span><br><span class="line">group by student_id </span><br><span class="line"></span><br><span class="line">having count(course_id)= (select count(course_id) <span class="keyword">from</span> score where student_id=<span class="number">2</span>);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">3</span>、删除学习“叶平”老师课的SC(score)表记录；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查出李平老师的id</span></span><br><span class="line"></span><br><span class="line">select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看李平老师所教授的课程</span></span><br><span class="line"></span><br><span class="line">select cid <span class="keyword">from</span> course where teacher_id = (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看李平老师所教课程的成绩数据</span></span><br><span class="line"></span><br><span class="line">select * <span class="keyword">from</span> score where course_id <span class="keyword">in</span> (select cid <span class="keyword">from</span> course where teacher_id = (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment"># 执行删除命令</span></span><br><span class="line"></span><br><span class="line">delete <span class="keyword">from</span> score where course_id <span class="keyword">in</span> (select cid <span class="keyword">from</span> course where teacher_id = (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="number">4</span>、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“<span class="number">002</span>”课程的同学学号；②插入“<span class="number">002</span>”号课程的平均成绩； </span><br><span class="line"></span><br><span class="line"><span class="comment">#  先找寻上过2号课程的同学</span></span><br><span class="line"></span><br><span class="line">select student_id <span class="keyword">from</span> score where course_id = <span class="number">2</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 再找到没上过2号课程的所有同学</span></span><br><span class="line"></span><br><span class="line">select * <span class="keyword">from</span> student where sid <span class="keyword">not</span> <span class="keyword">in</span> (select student_id <span class="keyword">from</span> score where course_id = <span class="number">2</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">#  计算出学习2号课程的同学的平均成绩</span></span><br><span class="line"></span><br><span class="line">select avg(num) <span class="keyword">from</span> score where course_id = <span class="number">2</span> group by course_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 用笛卡尔积将上述两个表拼起来</span></span><br><span class="line"></span><br><span class="line">select * <span class="keyword">from</span> (select sid <span class="keyword">from</span> student where sid <span class="keyword">not</span> <span class="keyword">in</span> (select student_id <span class="keyword">from</span> score where course_id = <span class="number">2</span>)) t1,(select avg(num) <span class="keyword">from</span> score where course_id = <span class="number">2</span> group by course_id) t2;</span><br><span class="line"></span><br><span class="line"><span class="comment">#  向SC表中插入记录</span></span><br><span class="line"></span><br><span class="line">insert into score (course_id,student_id,num)   select <span class="number">2</span>,t1.sid,t2.avg_num <span class="keyword">from</span> (select sid <span class="keyword">from</span> student where sid <span class="keyword">not</span> <span class="keyword">in</span> (select student_id <span class="keyword">from</span> score where course_id = <span class="number">2</span>)) t1,(select avg(num) avg_num <span class="keyword">from</span> score where course_id = <span class="number">2</span> group by course_id) t2;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">5</span>、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看每个学生的数学成绩</span></span><br><span class="line"></span><br><span class="line">select student_id,num <span class="keyword">from</span> score where course_id = (select cid <span class="keyword">from</span> course where cname = <span class="string">'数学'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">#  查看每个学生的语文成绩</span></span><br><span class="line"></span><br><span class="line">select student_id,num <span class="keyword">from</span> score where course_id = (select cid <span class="keyword">from</span> course where cname = <span class="string">'语文'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">#  查看每个学生的英语成绩</span></span><br><span class="line"></span><br><span class="line">select student_id,num <span class="keyword">from</span> score where course_id = (select cid <span class="keyword">from</span> course where cname = <span class="string">'英语'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看每个学生的平均成绩</span></span><br><span class="line"></span><br><span class="line">select student_id,avg(num),count(num) <span class="keyword">from</span> score group by student_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 将上面的几张表拼接起来,为了生成所有学生的信息，用student表作为左连接的第一张表</span></span><br><span class="line"></span><br><span class="line">select sid 学生ID,t2.num 语文,t1.num 数学, t3.num 英语,t4.count_course 有效课程数,t4.avg_num 有效平均分 <span class="keyword">from</span> student </span><br><span class="line"></span><br><span class="line"> left join (select student_id,num <span class="keyword">from</span> score where course_id = (select cid <span class="keyword">from</span> course where cname = <span class="string">'数学'</span>)) t1</span><br><span class="line"></span><br><span class="line"> on student.sid = t1.student_id</span><br><span class="line"></span><br><span class="line"> left join (select student_id,num <span class="keyword">from</span> score where course_id = (select cid <span class="keyword">from</span> course where cname = <span class="string">'语文'</span>)) t2</span><br><span class="line"></span><br><span class="line"> on student.sid = t2.student_id</span><br><span class="line"></span><br><span class="line"> left join (select student_id,num <span class="keyword">from</span> score where course_id = (select cid <span class="keyword">from</span> course where cname = <span class="string">'英语'</span>)) t3</span><br><span class="line"></span><br><span class="line"> on student.sid = t3.student_id</span><br><span class="line"></span><br><span class="line"> left join (select student_id,avg(num) avg_num,count(num) count_course <span class="keyword">from</span> score group by student_id)  t4</span><br><span class="line"></span><br><span class="line"> on student.sid = t4.student_id</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">6</span>、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；</span><br><span class="line"></span><br><span class="line">select course_id 课程ID,max(num) 最高分,min(num) 最低分 <span class="keyword">from</span> score group by course_id;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">7</span>、按各科平均成绩从低到高和及格率的百分数从高到低顺序；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 方法1:</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 先求平均成绩</span></span><br><span class="line"></span><br><span class="line">select course_id,avg(num) <span class="keyword">from</span> score group by course_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 解决计算各科及格率的问题</span></span><br><span class="line"></span><br><span class="line">所有及格的人/所有人数</span><br><span class="line"></span><br><span class="line">select t1.course_id,t1.count1/t2.count2 <span class="keyword">from</span> </span><br><span class="line"></span><br><span class="line">(select course_id,count(course_id) count1 <span class="keyword">from</span> score where num&gt;<span class="number">60</span> group by course_id) t1 </span><br><span class="line"></span><br><span class="line">left join</span><br><span class="line"></span><br><span class="line">(select course_id,count(course_id) count2 <span class="keyword">from</span> score group by course_id) t2</span><br><span class="line"></span><br><span class="line">on t1.course_id = t2.course_id;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 根据上述内容进行表的拼接</span></span><br><span class="line"></span><br><span class="line">select  t_out1.course_id,t_out1.avgnum, t_out2.pass_per <span class="keyword">from</span> </span><br><span class="line"></span><br><span class="line">(select course_id,avg(num) avgnum <span class="keyword">from</span> score group by course_id ) t_out1</span><br><span class="line"></span><br><span class="line">left join </span><br><span class="line"></span><br><span class="line">(select t1.course_id,t1.count1/t2.count2 pass_per <span class="keyword">from</span> </span><br><span class="line"></span><br><span class="line">(select course_id,count(course_id) count1 <span class="keyword">from</span> score where num&gt;<span class="number">60</span> group by course_id) t1 </span><br><span class="line"></span><br><span class="line">left join</span><br><span class="line"></span><br><span class="line">(select course_id,count(course_id) count2 <span class="keyword">from</span> score group by course_id) t2</span><br><span class="line"></span><br><span class="line">on t1.course_id = t2.course_id) t_out2</span><br><span class="line"></span><br><span class="line">on  t_out1.course_id = t_out2.course_id</span><br><span class="line"></span><br><span class="line"><span class="comment"># 加上排序</span></span><br><span class="line"></span><br><span class="line">select  t_out1.course_id,t_out1.avgnum, t_out2.pass_per <span class="keyword">from</span>  (select course_id,avg(num) avgnum <span class="keyword">from</span> score group by course_id ) t_out1 left join  (select t1.course_id,t1.count1/t2.count2 pass_per <span class="keyword">from</span>  (select course_id,count(course_id) count1 <span class="keyword">from</span> score where num&gt;<span class="number">60</span> group by course_id) t1  left join (select course_id,count(course_id) count2 <span class="keyword">from</span> score group by course_id) t2 on t1.course_id = t2.course_id) t_out2 on  t_out1.course_id = t_out2.course_id order by avgnum ,pass_per desc;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="comment"># 方法2 </span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 使用case when直接计算合格率</span></span><br><span class="line"></span><br><span class="line">select </span><br><span class="line"></span><br><span class="line">sum(case when num&gt;<span class="number">60</span> then <span class="number">1</span> <span class="keyword">else</span> <span class="number">0</span> end)/count(course_id)</span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> score group by course_id</span><br><span class="line"></span><br><span class="line"><span class="comment"># 加上课程id和平均值</span></span><br><span class="line"></span><br><span class="line">select  course_id,avg(num),</span><br><span class="line"></span><br><span class="line">sum(case when num&gt;<span class="number">60</span> then <span class="number">1</span> <span class="keyword">else</span> <span class="number">0</span> end)/count(course_id)</span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> score group by course_id</span><br><span class="line"></span><br><span class="line"><span class="comment"># 加上排序</span></span><br><span class="line"></span><br><span class="line">select  course_id,avg(num) avgnum,</span><br><span class="line"></span><br><span class="line">sum(case when num&gt;<span class="number">60</span> then <span class="number">1</span> <span class="keyword">else</span> <span class="number">0</span> end)/count(course_id) pass_per </span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> score group by course_id</span><br><span class="line"></span><br><span class="line"> order by avgnum ,pass_per desc;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">8</span>、查询各科成绩前三名的记录:(不考虑成绩并列情况) </span><br><span class="line"></span><br><span class="line">select</span><br><span class="line">t1.sid,t1.student_id,t1.course_id,t1.num <span class="keyword">from</span> score t1</span><br><span class="line">left join</span><br><span class="line">    (</span><br><span class="line">    select sid,course_id,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">0</span>, <span class="number">1</span>) <span class="keyword">as</span> first_num,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">1</span>, <span class="number">1</span>) <span class="keyword">as</span> second_num,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">2</span>, <span class="number">1</span>) <span class="keyword">as</span> third_num</span><br><span class="line">    <span class="keyword">from</span> score <span class="keyword">as</span> s1</span><br><span class="line">    ) t2</span><br><span class="line">on t1.sid = t2.sid</span><br><span class="line">where t1.num = t2.first_num <span class="keyword">or</span> t1.num = t2.second_num <span class="keyword">or</span> t1.num = t2.third_num;</span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="number">9</span>、查询每门课程被选修的学生数；</span><br><span class="line"></span><br><span class="line">select course_id,count(course_id) <span class="keyword">from</span> score group by course_id;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">10</span>、查询同名同姓学生名单，并统计同名人数；</span><br><span class="line"></span><br><span class="line">select sname,count(<span class="number">1</span>) <span class="keyword">as</span> count <span class="keyword">from</span> student group by sname;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">11</span>、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；</span><br><span class="line"></span><br><span class="line">select course_id,avg(<span class="keyword">if</span>(isnull(num), <span class="number">0</span> ,num)) <span class="keyword">as</span> avg <span class="keyword">from</span> score group by course_id order by avg  asc,course_id desc;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">12</span>、查询平均成绩大于<span class="number">85</span>的所有学生的学号、姓名和平均成绩；</span><br><span class="line"></span><br><span class="line">select student_id,sname, avg(<span class="keyword">if</span>(isnull(num), <span class="number">0</span> ,num)) <span class="keyword">from</span> score left join student on score.student_id = student.sid group by student_id;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">13</span>、查询课程名称为“数学”，且分数低于<span class="number">60</span>的学生姓名和分数；</span><br><span class="line"></span><br><span class="line">select student.sname,score.num <span class="keyword">from</span> score</span><br><span class="line"></span><br><span class="line">left join course on score.course_id = course.cid</span><br><span class="line"></span><br><span class="line">left join student on score.student_id = student.sid</span><br><span class="line"></span><br><span class="line">where score.num &lt; <span class="number">60</span> <span class="keyword">and</span> course.cname = <span class="string">'数学'</span></span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">14</span>、查询课程编号为<span class="number">003</span>且课程成绩在<span class="number">80</span>分以上的学生的学号和姓名； </span><br><span class="line"></span><br><span class="line">select * <span class="keyword">from</span> score where score.student_id = <span class="number">3</span> <span class="keyword">and</span> score.num &gt; <span class="number">80</span></span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">15</span>、求选了课程的学生人数</span><br><span class="line"></span><br><span class="line">select sid,sname <span class="keyword">from</span> student where sid <span class="keyword">not</span> <span class="keyword">in</span> (select student_id <span class="keyword">from</span> score group by student_id);</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">16</span>、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先找到“杨艳”老师的教师id</span></span><br><span class="line"></span><br><span class="line">select tid <span class="keyword">from</span> teacher where tname = <span class="string">'杨艳'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 再找到杨艳老师教的所有课程</span></span><br><span class="line"></span><br><span class="line">select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'杨艳'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment"># 再找到杨艳老师教的所有课程的最高分</span></span><br><span class="line"></span><br><span class="line">select max(num) <span class="keyword">from</span> score where course_id <span class="keyword">in</span> (select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment"># 再找到杨艳老师教的所有课程的最高分对应的学生</span></span><br><span class="line"></span><br><span class="line">select distinct student_id,num <span class="keyword">from</span> score </span><br><span class="line"></span><br><span class="line">where num = (select max(num) <span class="keyword">from</span> score where course_id <span class="keyword">in</span> (select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>))) </span><br><span class="line"></span><br><span class="line"><span class="keyword">and</span> course_id <span class="keyword">in</span>   (select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment"># 找到学生的姓名</span></span><br><span class="line"></span><br><span class="line">select student.sname,t1.num <span class="keyword">from</span>(</span><br><span class="line"></span><br><span class="line">select distinct student_id,num <span class="keyword">from</span> score </span><br><span class="line"></span><br><span class="line">where num = (select max(num) <span class="keyword">from</span> score where course_id <span class="keyword">in</span> (select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>))) </span><br><span class="line"></span><br><span class="line"><span class="keyword">and</span> course_id <span class="keyword">in</span>   (select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>))</span><br><span class="line"></span><br><span class="line">) t1</span><br><span class="line"></span><br><span class="line">left join</span><br><span class="line"></span><br><span class="line">student</span><br><span class="line"></span><br><span class="line">on </span><br><span class="line"></span><br><span class="line">t1.student_id = student.sid;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">17</span>、查询各个课程及相应的选修人数；</span><br><span class="line"></span><br><span class="line">select course.cname,count(<span class="number">1</span>) <span class="keyword">from</span> score</span><br><span class="line"></span><br><span class="line">left join course on score.course_id = course.cid</span><br><span class="line"></span><br><span class="line">group by course_id;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">18</span>、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；</span><br><span class="line"></span><br><span class="line">select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num <span class="keyword">from</span> score <span class="keyword">as</span> s1, score <span class="keyword">as</span> s2 where s1.num = s2.num <span class="keyword">and</span> s1.course_id != s2.course_id;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">19</span>、查询每门课程成绩最好的前两名；</span><br><span class="line"></span><br><span class="line">   先查询每条数据对应学科成绩的第一名和第二名，这里必须要保留所有的s1，以便后续进行连表查询</span><br><span class="line"></span><br><span class="line">select sid,course_id,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">0</span>, <span class="number">1</span>) <span class="keyword">as</span> first_num,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">1</span>, <span class="number">1</span>) <span class="keyword">as</span> second_num</span><br><span class="line"><span class="keyword">from</span> score <span class="keyword">as</span> s1</span><br><span class="line"></span><br><span class="line">按照sid连表，把学生的成绩和对应的第一名、第二名成绩连起来</span><br><span class="line">select</span><br><span class="line">* <span class="keyword">from</span> score t1</span><br><span class="line">left join</span><br><span class="line">    (</span><br><span class="line">    select sid,course_id,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">0</span>, <span class="number">1</span>) <span class="keyword">as</span> first_num,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">1</span>, <span class="number">1</span>) <span class="keyword">as</span> second_num</span><br><span class="line">    <span class="keyword">from</span> score <span class="keyword">as</span> s1</span><br><span class="line">    ) t2</span><br><span class="line">on t1.sid = t2.sid</span><br><span class="line"></span><br><span class="line">判断如果学生的成绩是第一名、第二名的成绩，那么就符合条件，显示学生的id、学科和成绩</span><br><span class="line">select</span><br><span class="line">t1.sid,t1.student_id,t1.course_id,t1.num <span class="keyword">from</span> score t1</span><br><span class="line">left join</span><br><span class="line">    (</span><br><span class="line">    select sid,course_id,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">0</span>, <span class="number">1</span>) <span class="keyword">as</span> first_num,</span><br><span class="line">    (select num <span class="keyword">from</span> score <span class="keyword">as</span> s2 where s2.course_id = s1.course_id order by num desc limit <span class="number">1</span>, <span class="number">1</span>) <span class="keyword">as</span> second_num</span><br><span class="line">    <span class="keyword">from</span> score <span class="keyword">as</span> s1</span><br><span class="line">    ) t2</span><br><span class="line">on t1.sid = t2.sid</span><br><span class="line">where t1.num = t2.first_num <span class="keyword">or</span> t1.num = t2.second_num;</span><br><span class="line"></span><br><span class="line"><span class="number">20</span>、检索至少选修两门课程的学生学号；</span><br><span class="line"></span><br><span class="line">select student_id <span class="keyword">from</span> score group by student_id having count(student_id) &gt; <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line"><span class="number">21</span>、查询全部学生都选修的课程的课程号和课程名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查看一共有多少学生</span></span><br><span class="line"></span><br><span class="line">select count(sid) <span class="keyword">from</span> student;</span><br><span class="line"></span><br><span class="line"><span class="comment">#  查看哪一门课选秀的学生个数和学生的总个数相等</span></span><br><span class="line"></span><br><span class="line">select course_id <span class="keyword">from</span> score group by course_id having count(student_id) = (select count(sid) <span class="keyword">from</span> student);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="number">22</span>、查询没学过“叶平”老师讲授的任一门课程的学生姓名；</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先查看要查找老师的id</span></span><br><span class="line"></span><br><span class="line">select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看该老师交了哪些课程</span></span><br><span class="line"></span><br><span class="line">select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 看看有多少学生学习了该老师的课程</span></span><br><span class="line"></span><br><span class="line">select distinct student_id <span class="keyword">from</span> score where course_id <span class="keyword">in</span> (select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>));</span><br><span class="line"></span><br><span class="line"><span class="comment"># 把不在上表中的学生姓名查出来</span></span><br><span class="line"></span><br><span class="line">select sname <span class="keyword">from</span> student where sid <span class="keyword">not</span> <span class="keyword">in</span> (select distinct student_id <span class="keyword">from</span> score where course_id <span class="keyword">in</span> (select cid <span class="keyword">from</span> course where teacher_id <span class="keyword">in</span> (select tid <span class="keyword">from</span> teacher where tname = <span class="string">'李平老师'</span>)));</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="number">23</span>、查询两门以上不及格课程的同学的学号及其平均成绩；</span><br><span class="line"></span><br><span class="line">select student_id,avg(num) <span class="keyword">from</span> score where num&lt;<span class="number">60</span> group by student_id having count(num)&gt;=<span class="number">2</span>;</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"><span class="number">24</span>、检索“<span class="number">004</span>”课程分数小于<span class="number">60</span>，按分数降序排列的同学学号；</span><br><span class="line"></span><br><span class="line">select student_id <span class="keyword">from</span> score where num&lt; <span class="number">60</span> <span class="keyword">and</span> course_id = <span class="number">4</span> order by num desc;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="number">25</span>、删除“<span class="number">002</span>”同学的“<span class="number">001</span>”课程的成绩；</span><br><span class="line"></span><br><span class="line">delete <span class="keyword">from</span> score where course_id = <span class="number">1</span> <span class="keyword">and</span> student_id = <span class="number">2</span>；</span><br></pre></td></tr></table></figure></section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/python/MySQL%E5%85%A5%E9%97%A8/4-Linux%E5%AE%89%E8%A3%85MySQL/">
        <span class="nav-arrow">← </span>
        
          python/MySQL入门/4-Linux安装MySQL
        
      </a>
    
    
      <a class="nav-right" href="/python/MySQL%E5%85%A5%E9%97%A8/12-MySQL%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2/">
        
          python/MySQL入门/12-MySQL多表查询
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#一、综合练习"><span class="toc-nav-text">一、综合练习</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-1-init-sql文件内容"><span class="toc-nav-text">1.1 init.sql文件内容</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-2-从init-sql文件中导入数据"><span class="toc-nav-text">1.2 从init.sql文件中导入数据</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-3-基础练习"><span class="toc-nav-text">1.3 基础练习</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-4-进阶练习"><span class="toc-nav-text">1.4 进阶练习</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#二、基础练习答案"><span class="toc-nav-text">二、基础练习答案</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#三、进阶练习答案"><span class="toc-nav-text">三、进阶练习答案</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/python/MySQL入门/13-MySQL多表查询综合练习答案/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "python/MySQL入门/13-MySQL多表查询综合练习答案",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>